package com.itbz.mapper;

import com.itbz.pojo.Page;
import com.itbz.pojo.Users;
import com.itbz.util.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * 分页查询测试类
 */
public class PageTest {


  /**
   * 分页查询Users
   */
  public Page<Users> selectPage(Page page){
    Connection conn =null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    List<Users> list = new ArrayList<>();
    try{
      //获取连接对象
      conn = JdbcUtils.getConnection();
      //创建PrepareStatement对象
      ps = conn.prepareStatement("select * from users limit ?,?");
      //绑定m参数  m的值 = (当前页-1)*每页显示的条数
      ps.setInt(1,(page.getCurrentPage()-1)*page.getPageSize());
      //绑定n参数 n的值为每页显示的条数
      ps.setInt(2,page.getPageSize());
      //执行查询
      rs = ps.executeQuery();
      //处理结果集
      while(rs.next()){
        //完成ORM映射
        Users users = new Users();
        users.setUserid(rs.getInt("userid"));
        users.setUsername(rs.getString("username"));
        users.setUserage(rs.getInt("userage"));
        list.add(users);
       }
      //讲结果集存放到Page对象中。
      page.setResult(list);

      //查询总条数
      ps = conn.prepareStatement("select count(*) from users");
      //执行查询
      rs = ps.executeQuery();
      while(rs.next()){
        //总条数
       int count = rs.getInt(1);
        //保存总条数
        page.setTotalCount(count);
        //换算总页数 = 总条数 / 每页显示的条数 向上取整
        int totalPage = (int)Math.ceil(1.0*count/page.getPageSize());
        //保存总页数
        page.setTotalPage(totalPage);
       }
     }catch(Exception e){
      e.printStackTrace();
     }finally{
      JdbcUtils.closeResource(rs,ps,conn);
     }
    return page;
   }


  public static void main(String[] args) {
    PageTest pt = new PageTest();
    Page page = new Page();
    page.setCurrentPage(2);
    page.setPageSize(2);
    Page page1 = pt.selectPage(page);
    System.out.println("总条数："+page1.getTotalCount());
    System.out.println("总页数："+page1.getTotalPage());
    System.out.println("当前页："+page1.getCurrentPage());
    System.out.println("每页显示的条数："+page1.getPageSize());
    List<Users> list = page1.getResult();
    for(Users user:list){
      System.out.println(user.getUserid()+ " "+user.getUsername()+" "+user.getUserage());
     }
   }
}
